﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class ThietBiDAO
    {
        public List<ThietBiDTO> LayDanhSachThietBi()
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachThietBi";
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> LayDanhSachThietBiTheoMaLoaiThietBi(int ma)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayDanhSachThietBiTheoMaLoaiThietBi";
            cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public ThietBiDTO LayThongTinThietBi(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayThongTinThietBi";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            ThietBiDTO dto = new ThietBiDTO();
            dr.Read();
            //dto.MaThietBi = dr.GetInt32(0);
            dto.TenThietBi = dr.GetString(1);
            dto.MaLoaiThietBi = dr.GetInt32(2);
            dto.XuatXu = dr.GetString(3);
            dto.ThongTinMoTa = dr.GetString(4);
            dto.HinhAnh = dr.GetString(5);
            //dto.MaNhaCungCap = dr.GetInt32(6);
            dto.IMEI = dr.GetString(6);
            dto.GiaNhap = dr.GetDouble(7);
            dto.GiaBan = dr.GetDouble(8);
            dto.SoLuongTon = dr.GetInt32(9);
            dr.Close();
            conn.Close();
            return dto;
        }

        public int LaySoLuongThietBi()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LaySoLuongThietBi";
            int sl = (int)cm.ExecuteScalar();
            conn.Close();
            return sl;
        }

        public int LaySoLuongTon(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LaySoLuongTon";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            int sl = (int)cm.ExecuteScalar();
            conn.Close();
            return sl;
        }

        public int LaySoLuongThietBiTheoMaLoaiThietBi(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LaySoLuongThietBiTheoMaLoaiThietBi";
            cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = ma;
            int sl = (int)cm.ExecuteScalar();
            conn.Close();
            return sl;
        }

        public int LayMaThietBiCaoNhat()
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_LayMaThietBiCaoNhat";
            int ma = (int)cm.ExecuteScalar();
            conn.Close();
            return ma;
        }

        public void ThemThietBi(ThietBiDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_ThemThietBi";
            cm.Parameters.Add("@TenThietBi", SqlDbType.NVarChar).Value = dto.TenThietBi;
            cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = dto.MaLoaiThietBi;
            cm.Parameters.Add("@XuatXu", SqlDbType.NVarChar).Value = dto.XuatXu;
            cm.Parameters.Add("@ChucNang", SqlDbType.NVarChar).Value = dto.ThongTinMoTa;
            cm.Parameters.Add("@HinhAnh", SqlDbType.NVarChar).Value = dto.HinhAnh;
            //cm.Parameters.Add("@MaNhaCungCap", SqlDbType.Int).Value = dto.MaNhaCungCap;
            cm.Parameters.Add("@IMEI", SqlDbType.VarChar).Value = dto.IMEI;
            cm.Parameters.Add("@GiaNhap", SqlDbType.Float).Value = dto.GiaNhap;
            cm.Parameters.Add("@GiaBan", SqlDbType.Float).Value = dto.GiaBan;
            cm.Parameters.Add("@SoLuongTon", SqlDbType.Int).Value = dto.SoLuongTon;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void XoaThietBi(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_XoaThietBi";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void XoaThietBiTheoMaLoaiThietBi(int ma)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_XoaThietBiTheoMaLoaiThietBi";
            cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = ma;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatThietBi(ThietBiDTO dto)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatThietBi";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = dto.MaThietBi;
            cm.Parameters.Add("@TenThietBi", SqlDbType.NVarChar).Value = dto.TenThietBi;
            //cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = dto.MaLoaiThietBi;
            cm.Parameters.Add("@XuatXu", SqlDbType.NVarChar).Value = dto.XuatXu;
            cm.Parameters.Add("@ThongTinMoTa", SqlDbType.NVarChar).Value = dto.ThongTinMoTa;
            cm.Parameters.Add("@HinhAnh", SqlDbType.NVarChar).Value = dto.HinhAnh;
            //cm.Parameters.Add("@MaNhaCungCap", SqlDbType.Int).Value = dto.MaNhaCungCap;
            cm.Parameters.Add("@IMEI", SqlDbType.VarChar).Value = dto.IMEI;
            cm.Parameters.Add("@GiaNhap", SqlDbType.Float).Value = dto.GiaNhap;
            cm.Parameters.Add("@GiaBan", SqlDbType.Float).Value = dto.GiaBan;
            cm.Parameters.Add("@SoLuongTon", SqlDbType.Int).Value = dto.SoLuongTon;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatSoLuongTon(int ma, int sl)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatSoLuongTon";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            cm.Parameters.Add("@SoLuong", SqlDbType.Int).Value = sl;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatGiaNhap(int ma, double gia)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatGiaNhap";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            cm.Parameters.Add("@Gia", SqlDbType.Float).Value = gia;
            cm.ExecuteNonQuery();
            conn.Close();
        }

        public void CapNhatGiaBan(int ma, double gia)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_CapNhatGiaBan";
            cm.Parameters.Add("@MaThietBi", SqlDbType.Int).Value = ma;
            cm.Parameters.Add("@Gia", SqlDbType.Float).Value = gia;
            cm.ExecuteNonQuery();
            conn.Close();
        }
        
        public List<ThietBiDTO> TimKiemThietBiTatCaTieuChi(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTatCaTieuChi";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoLoaiThietBiTatCaTieuChi(string str, int ma)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoLoaiThietBiTatCaTieuChi";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            cm.Parameters.Add("@MaLoaiThietBi", SqlDbType.Int).Value = ma;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoTen(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoTen";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoLoaiThietBi(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoLoaiThietBi";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoXuatXu(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoXuatXu";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoChucNang(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoChucNang";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.NVarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoIMEI(string str)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoIMEI";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.VarChar).Value = str;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoGia(double gia)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoGia";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.Float).Value = gia;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }

        public List<ThietBiDTO> TimKiemThietBiTheoSoLuongTon(int sl)
        {
            List<ThietBiDTO> ds = new List<ThietBiDTO>();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand cm = new SqlCommand();
            cm.Connection = conn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.CommandText = "usp_TimKiemThietBiTheoSoLuongTon";
            cm.Parameters.Add("@ChuoiTimKiem", SqlDbType.Int).Value = sl;
            SqlDataReader dr = cm.ExecuteReader();
            while (dr.Read())
            {
                ThietBiDTO dto = new ThietBiDTO();
                dto.MaThietBi = dr.GetInt32(0);
                dto.TenThietBi = dr.GetString(1);
                dto.MaLoaiThietBi = dr.GetInt32(2);
                dto.XuatXu = dr.GetString(3);
                dto.ThongTinMoTa = dr.GetString(4);
                dto.HinhAnh = dr.GetString(5);
                //dto.MaNhaCungCap = dr.GetInt32(6);
                dto.IMEI = dr.GetString(6);
                dto.GiaNhap = dr.GetDouble(7);
                dto.GiaBan = dr.GetDouble(8);
                dto.SoLuongTon = dr.GetInt32(9);
                ds.Add(dto);
            }
            dr.Close();
            conn.Close();
            return ds;
        }
    }
}
